package org.formal.common.utils;

import java.io.BufferedReader;
import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.util.Formatter;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.format.CellFormatResult;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRElt;
import org.springframework.util.ResourceUtils;
 
/**
 * 
 * @author Ken Arnold, Industrious Media LLC
 */
public class ExcelToHtml {
    private final Workbook wb;
    private final Appendable output;
    private boolean completeHTML;
    private Formatter out;
    private boolean gotBounds;
    private int firstColumn;
    private int endColumn;
    private HashSet<String> cellMerged= new HashSet<String>();
    private HashSet<String> cellHasValue= new HashSet<String>();
    private HashSet<String> cellHidden = new HashSet<String>();
    private Map<String,String> cellMergedPrint= new HashMap<String,String>();
 
    private static final String DEFAULTS_CLASS = "excelDefaults";
 
    /**
     * 1.创建一个excel文件转换为html文件的样式模板
     * @param wb Workbook excel工作表
     * @param output Appendable 接口类
     * @return 返回类对象
     */
    public static ExcelToHtml create(Workbook wb, Appendable output) {
        return new ExcelToHtml(wb, output);
    }
 
    /**
     * 1.创建一个excel文件转换为html文件的样式模板
     * @param path 文件路径
     * @param output Appendable 接口类
     * @return 返回类对象
     * @throws IOException 文件转换错误异常
     */
    public static ExcelToHtml create(String path, Appendable output)
            throws IOException {
        return create(new FileInputStream(path), output);
    }
 
   /**
    * 1.创建一个excel文件转换为html文件的样式模板
    * @param in 字节流
    * @param output Appendable 接口类
    * @return 返回类对象
    * @throws IOException 文件转换错误异常
    */
    public static ExcelToHtml create(InputStream in, Appendable output)
            throws IOException {
        try {
            Workbook wb = WorkbookFactory.create(in);
            return create(wb, output);
        } catch (InvalidFormatException e){
            throw new IllegalArgumentException("Cannot create workbook from stream", e);
        }
    }
    
    /**
     * 内部构造方法
     * @param wb
     * @param output
     */
    private ExcelToHtml(Workbook wb, Appendable output) {
        if (wb == null) {
        	throw new NullPointerException("wb");
        }
        if (output == null) {
        	throw new NullPointerException("output");
        }
        this.wb = wb;
        this.output = output;
    }
 
    /**
     * 测试 excel 转换为 html
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
         ExcelToHtml toHtml = ExcelToHtml.create("C:/Users/jiangyan.JINZSOFT/Desktop/预算项目支出绩效目标监控表.xlsx", new PrintWriter(new File("F:/upload/excel.html"),"utf-8"));
	     toHtml.setCompleteHTML(true);
	     toHtml.printPage();
    }
    /**
     * 2.设置是否包含html样式
     * @param completeHTML true 包含  false 不包含
     */
    public void setCompleteHTML(boolean completeHTML) {
        this.completeHTML = completeHTML;
    }
    
    /**
     * 3.输出内容
     * @throws IOException
     */
    public void printPage() throws IOException {
        try {
            ensureOut();
            if (completeHTML) {
                out.format("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>%n");
                out.format("<html>%n");
                out.format("<head>%n");
                out.format("</head>%n");
                out.format("<body>%n");
            }
            print();
            if (completeHTML) {
                out.format("</body>%n");
                out.format("</html>%n");
            }
        } finally {
            if (out != null) {
            	out.close();
            }
            if (output instanceof Closeable) {
                Closeable closeable = (Closeable) output;
                closeable.close();
            }
        }
    }
    
    /**
     * 4.调用样式 和生成多个sheet
     */
    public void print() {
        printInlineStyle();
        printSheets();
    }
    
    /**
     * 5.在线输出表格样式
     */
    private void printInlineStyle() {
        out.format("<style type=\"text/css\">%n");
        printStyles();
        out.format("</style>%n");
    }
    
    /**
     * 验证out 不能为空
     */
    private void ensureOut() {
        if (out == null) {
        	out = new Formatter(output);
        }
    }
    
    /**
     * 6.获取excelStyle.css 样式 并输出
     */
    public void printStyles() {
        ensureOut();
        BufferedReader in = null;
        try {
        	File file = ResourceUtils.getFile("classpath:excelStyle.css");
        	FileInputStream fileIn = new FileInputStream(file);
            in = new BufferedReader(new InputStreamReader(fileIn));
            String line;
            while ((line = in.readLine()) != null) {
                out.format("%s%n", line);
            }
        } catch (IOException e) {
            throw new IllegalStateException("Reading standard css", e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    throw new IllegalStateException("Reading standard css", e);
                }
            }
        }
        
        /**
         * 将样式设置为默认样式 并放入seen中
         */
        Set<CellStyle> seen = new HashSet<CellStyle>();
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                Row row = rows.next();
                for (Cell cell : row) {
                    CellStyle style = cell.getCellStyle();
                    if (!seen.contains(style)) {
                        printStyle(style);
                        seen.add(style);
                    }
                }
            }
        }
    }
 
    private void printStyle(CellStyle style) {
        out.format(".%s .%s {%n", DEFAULTS_CLASS, styleName(style));
        styleContents(style);
        out.format("}%n");
    }
    /**
     * 设置样式 垂直布局 水平布局 字体颜色 字体权重 是否斜体 字体大小 字体类型
     * @param style
     */
    private void styleContents(CellStyle style) {
    	HorizontalAlignment alignment = style.getAlignmentEnum();
    	VerticalAlignment verticalAlignment = style.getVerticalAlignmentEnum();
        styleOut("text-align", alignment.name());
        styleOut("vertical-align", verticalAlignment.name());
        fontStyle(style);
        colorStyles(style, out);
    }
 
    private String fontStyleDetail(Font font){
    	StringBuffer buf = new StringBuffer("");
		XSSFFont font1 = (XSSFFont)font;
		if(!(font1.getXSSFColor()==null||font1.getXSSFColor().isAuto())) {
			buf.append("color:#"+font1.getXSSFColor().getARGBHex().substring(2)+";");
		}
    	 if (font.getBold()){
    		 buf.append("font-weight: bold;");
    	 }else{
    		 buf.append("font-weight: normal;");
    	 }     
    	 if (font.getItalic()) {
    		 buf.append("font-style:italic;");
    	 }
    	 buf.append("font-family:"+font.getFontName()+";");
    	 buf.append("font-size:"+font.getFontHeightInPoints()+"pt;");
    	 return buf.toString();
    }
    
    private void fontStyle(CellStyle style) {
        Font font = wb.getFontAt(style.getFontIndex());
        if (font.getBold()) {
        	out.format("  font-weight: bold;%n");
        }
        if (font.getItalic()){
        	out.format("  font-style: italic;%n");
        }
        out.format("  font-family: %s;%n",font.getFontName()); 
        int fontheight = font.getFontHeightInPoints();
        int noHeight = 9;
        if (fontheight == noHeight) {
            fontheight = 10;
        }
        out.format("  font-size: %dpt;%n", fontheight);
    }
 
    private String styleName(CellStyle style) {
        if (style == null) {
        	style = wb.getCellStyleAt((short) 0);
        }
        StringBuilder sb = new StringBuilder();
        Formatter fmt = new Formatter(sb);
        fmt.format("style_%02x", style.getIndex());
        String name = fmt.toString();
        fmt.close();
        return name;
    }
 
    private <K> void styleOut(String attr, String value) {
        if (value != null) {
            out.format("  %s: %s;%n", attr, value);
        }
    }
    
    /**
     * 输出内容行
     */
    private void printSheets() {
        ensureOut();
        Sheet sheet = wb.getSheetAt(wb.getFirstVisibleTab());
        ensureColumnBounds(sheet);
        saprateCells(sheet);
        printSheet(sheet);
    }
 
   
 
	public void printSheet(Sheet sheet) {
        ensureOut();
        float width = 0f;
        //计算表格长度
        for(int i=firstColumn;i<endColumn;i++){
        	width += sheet.getColumnWidthInPixels(i) + 5f;
        }
        out.format("<div align=\"center\"><table class=%s cellspacing=\"0\" border=\"1\" cellpadding=\"0\" style=\"word-break:break-all;width:"+width+"px \">%n", DEFAULTS_CLASS);
        printSheetContent(sheet);
        out.format("</table>%n</div>%n");
    }
 
    private void ensureColumnBounds(Sheet sheet) {  
        if (gotBounds) {
        	return;
        }
        Iterator<Row> iter = sheet.rowIterator();
        firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
        endColumn = 0;
        while (iter.hasNext()) {
            Row row = iter.next();
            short firstCell = row.getFirstCellNum();
            if (firstCell >= 0) {
                firstColumn = Math.min(firstColumn, firstCell);
                endColumn = Math.max(endColumn, row.getLastCellNum());
            }
        }
        gotBounds = true;
    }
 
    private void printSheetContent(Sheet sheet) {
        out.format("<tbody>%n");
        sheet.getActiveCell();
        for(int num=sheet.getFirstRowNum();num<=sheet.getLastRowNum();num++) {
            Row row = sheet.getRow(num);
            if(row==null){
            	out.format("<tr><td >  </td></tr>%n");
            	continue;
            }
            if(row.getZeroHeight()) {
            	continue;
            }
            out.format("  <tr style=\"height:+"+row.getHeightInPoints()+"pt;\">%n");
            for(int j = firstColumn;j<endColumn;j++){
            	String content = " ";
          	  	String attrs = "";
          	  	CellStyle style = null;
            	String point = "("+j+","+num+")";
            	if(cellHidden.contains(point)) {
            		continue;
            	}
            	if(!cellHasValue.contains(point)){
            		 out.format("    <td class=%s %s>%s</td>%n", styleName(style),
                             attrs, content);
            		 continue;
            	}
            	Cell cell = row.getCell(j);
                if (shouldPrint(cell)) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    try {
						XSSFRichTextString rich = (XSSFRichTextString)cell.getRichStringCellValue();
						StringBuffer contents = new StringBuffer();
						if(rich.hasFormatting()){
							int startIndex = 0; 
							for(CTRElt ct : rich.getCTRst().getRList()){
								XSSFFont font =rich.getFontAtIndex(startIndex);
								startIndex += ct.getT().length();
								contents.append("<font style=\""+fontStyleDetail(font)+" \">"+ct.getT()+"</font>") ;
							}
							content = contents.toString();
						}else{
							content = rich.getString();
						}			
					} catch (Exception e) {
						 CellFormat cf;
	                    if(style.getDataFormatString()!=null){
	                    	 cf = CellFormat.getInstance(
	                                 style.getDataFormatString());
	                    }else{
	                    	 cf = CellFormat.getInstance(
	                                "General");
	                    }
	                    CellFormatResult result = cf.apply(cell);
	                    content = result.text;
					}
                    if ("".equals(content)) {
                    	content = " ";
                    }
                    out.format("    <td class=%s %s>%s</td>%n", styleName(style),
                            attrs, content.replaceAll("\\n", "<br/>"));
                }
            }
            out.format("  </tr>%n");
        }
        out.format("</tbody>%n");
    }
    
    private boolean shouldPrint(Cell cell){
       	String point = "("+cell.getAddress().getColumn()+","+cell.getAddress().getRow()+")";
    	if(cellMerged.contains(point)){
    		//不是第一次渲染则不渲染
    		if(!cellMergedPrint.containsKey(point)){
    			return false;
    		}
    	}
		return true;
    }
    
    private String tagStyle(Cell cell, CellStyle style) {
    	//调整align
    	StringBuffer buf = new StringBuffer("style=\"");
        //调整宽度
       // String width = cell.getSheet().getColumnWidthInPixels(cell.getColumnIndex())+"px;";
        Font font = wb.getFontAt(style.getFontIndex());
        //通过字体大小计算Cell宽度
        String width = cell.getSheet().getColumnWidth(cell.getColumnIndex())/256*font.getFontHeight()/20+"pt";
        buf.append("width:"+width);
        buf.append("\" ");
        String point = "("+cell.getAddress().getColumn()+","+cell.getAddress().getRow()+")";
        if(cellMergedPrint.containsKey(point)){
        	String[] str = cellMergedPrint.get(point).split(",");
        	int rowspan =Integer.parseInt(str[0]);
        	int colspan =Integer.parseInt(str[1]);
        	if (rowspan>1) {
        		buf.append("rowspan=\""+rowspan+"\" ");
        	}
        	if (colspan>1) {
        		buf.append("colspan=\""+colspan+"\" ");
        	}
        }
       
        return buf.toString();
    }
    
    
    public void colorStyles(CellStyle style, Formatter out) {
        XSSFCellStyle cs = (XSSFCellStyle) style;
        styleColor(out, "background-color", cs.getFillForegroundXSSFColor());
        styleColor(out, "color", cs.getFont().getXSSFColor());
    }
 
    private void styleColor(Formatter out, String attr, XSSFColor color) {
        if (color == null || StringUtils.isBlank(color.getARGBHex())) {
        	return;
        }
        out.format(" %s:#%s;%n",attr, color.getARGBHex().substring(2));
    }
    
    /**
     * 用于分组全并的单元格，与其中要打印的单元格
     * @author liuyizhi
     * */
    private void saprateCells(Sheet sheet) {
		for(CellRangeAddress addr :sheet.getMergedRegions()){
			 int rowspan = addr.getLastRow() - addr.getFirstRow()+1;
			 int colspan = addr.getLastColumn() - addr.getFirstColumn()+1;
			for(int x = addr.getFirstColumn();x <= addr.getLastColumn();x++) {
				for(int y=addr.getFirstRow();y<=addr.getLastRow();y++){
					cellMerged.add("("+x+","+y+")"); 
					if(x==addr.getFirstColumn()&&y==addr.getFirstRow()) {
						cellMergedPrint.put("("+x+","+y+")",rowspan+","+colspan);
					}
				}
			}
		}
		//过滤隐藏的列
		for(int i= firstColumn;i<endColumn;i++){
			if (sheet.isColumnHidden(i)) {
				for(int j = sheet.getFirstRowNum();j<=sheet.getLastRowNum();j++) {
					cellHidden.add("("+i+","+j+")");
				}
				
			}
		}	
		
		//过滤有值的cell
		Iterator<Row> iter = sheet.rowIterator();
		while(iter.hasNext()){
			Row row = iter.next();
			for(int i = row.getFirstCellNum();i<row.getLastCellNum();i++){
				Cell cell = row.getCell(i);
				
				if(cell ==null) {
					continue;
				}
				CellAddress address = cell.getAddress();
				cellHasValue.add("("+address.getColumn()+","+address.getRow()+")");
			}
			
		}
	}
}
